# python 连接sql server
import pymssql


sql_creat_version_info = """
IF OBJECT_ID('version_info', 'U') IS NOT NULL
    DROP TABLE version_info
CREATE TABLE version_info (
    id INT identity(1,1) not null,
    soft_name VARCHAR(255) not null, 
    version VARCHAR(100) not null,
	remarks	 TEXT,	
    PRIMARY KEY(id)
)
"""

sql_creat_m_code_info = """
IF OBJECT_ID('m_code_info', 'U') IS NOT NULL
    DROP TABLE m_code_info
CREATE TABLE m_code_info (
    id INT identity(1,1) not null,
	soft_name VARCHAR(255) not null,
    username VARCHAR(255) not null,
    m_code VARCHAR(255) not null,
    delete_status bit not null,
    remarks	TEXT,	
    PRIMARY KEY(id)
)
"""

sql_creat_shopname_info = """
IF OBJECT_ID('shopname_info', 'U') IS NOT NULL
    DROP TABLE shopname_info
CREATE TABLE shopname_info (
    id INT identity(1,1) not null,
	soft_name VARCHAR(255) not null,
    username VARCHAR(255) not null,
    shopname VARCHAR(255) not null,
    delete_status bit not null,
    remarks	TEXT,
    PRIMARY KEY(id)
)
"""

sql_notice_info = """
IF OBJECT_ID('notice_info', 'U') IS NOT NULL
    DROP TABLE notice_info
CREATE TABLE notice_info (
    id INT identity(1,1) not null,
	soft_name VARCHAR(255) not null,
    notice text not null,
    remarks	TEXT,
    PRIMARY KEY(id)
)
"""





class DB():

  def __init__(self):
    host = "47.111.170.81"  # 连接服务器地址
    user = "sa"  # 连接帐号
    password = "ZmE_feeling"  # 连接密码
    database = "ToolsData"  # 要连接的数据库
    self.conn = pymssql.connect(host, user, password, database, charset = "GBK")  #获取连接
    self.cursor = self.conn.cursor() # 获取光标

    if not self.cursor:
      # logger.info('数据库连接失败！')
      raise Exception('数据库连接失败！')
    else:
      # logger.info('数据库连接成功')
      pass
    self.sql_query_m_code = "SELECT m_code FROM {} WHERE delete_status=0 AND soft_name='大词表接口版'"
    self.sql_query_version = "SELECT version FROM {} WHERE soft_name='大词表接口版'"
    # self.sql_query_shopnames = "SELECT shopname FROM {} WHERE delete_status=0 AND soft_name='销量排序工具'"
    # self.sql_query_notice = "SELECT notice FROM {} WHERE soft_name='销量排序工具'"


  def create_table(self, sql):
    """
    创建数据表
    :param sql:建表语句
    :return:
    """
    self.cursor.execute(sql)
    self.conn.commit()


  def insert_data(self, sql):
    """插入数据"""
    self.cursor.executemany(sql)
    self.cursor.commit()


  def query_field(self, sql):
    """查询相关字段信息"""
    self.cursor.execute(sql)
    data_list = []
    for item in self.cursor.fetchall():
      data_list.append(item[0])

    return data_list

  def query_m_code(self):
    return self.query_field(self.sql_query_m_code.format('m_code_info'))


  def query_version(self):
    return self.query_field(self.sql_query_version.format('version_info'))

  # def query_shopnames(self):
  #   return self.query_field(self.sql_query_shopnames.format('shopname_info'))
  #
  # def query_notice(self):
  #   return self.query_field(self.sql_query_notice.format('notice_info'))



  def __del__(self):

    """对象销毁之前调用的方法"""
    self.conn.close()  # 销毁前关闭连接


db_ = DB()

if __name__ == '__main__':
  # host = "47.111.170.81"    # 连接服务器地址
  # user = "sa"               # 连接帐号
  # password = "ZmE_feeling"# 连接密码
  # database = "ZTCToolBox"  # 要连接的数据库
  # database = "SalesSort"  # 要连接的数据库
  db = DB()
  db.create_table(sql_creat_version_info)
  db.create_table(sql_creat_shopname_info)
  db.create_table(sql_creat_m_code_info)
  db.create_table(sql_notice_info)
  # print(db.query_m_code())
  # print(db.query_version())
  # print(db.query_shopnames())
  # print(db.query_notice())












# # 插入多行数据
# cursor.executemany(
#     "INSERT INTO m_code_info VALUES (%d, %s, %s)",
#     [(1, 'John Smith', 'John Doe'),
#      (2, 'Jane Doe', 'Joe Dog'),
#      (3, 'Mike T.', 'Sarah H.')])
# # 你必须调用 commit() 来保持你数据的提交如果你没有将自动提交设置为true


# # # 查询数据
# cursor.execute('SELECT version FROM version_info WHERE soft_name=%s', 'DMPTool')
#
# version = cursor.fetchone()[0]
# print(version)
#
#
# # 查询未删除状态的机器码
# cursor.execute('SELECT m_code FROM m_code_info WHERE delete_status=0')
#
# m_code_ = cursor.fetchall()
#
# m_code_list = []
# for m_codes in m_code_:
#     m_code_list.append(m_codes[0])
#
# print(m_code_list)
#
# # 查询未删除状态的店铺名称
# cursor.execute('SELECT shopname FROM shopname_info WHERE delete_status=0')
#
# shopname_ = cursor.fetchall()
#
# shopname_list = []
# for shopname in shopname_:
#     shopname_list.append(shopname[0])
#
# print(shopname_list)

# # 遍历数据（存放到元组中） 方式1
# row = cursor.fetchone()
# while row:
#     print("ID=%d, Name=%s" % (row[0], row[1]))
#     row = cursor.fetchone()
#
# # 遍历数据（存放到元组中） 方式2
# for row in cursor:
#     print('row = %r' % (row,))


# 遍历数据（存放到字典中）
# cursor = conn.cursor(as_dict=True)
#
# cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
# for row in cursor:
#     print("ID=%d, Name=%s" % (row['id'], row['name']))

# 注：在任何时候，在一个连接下，一次正在执行的数据库操作只会出现一个cursor对象